package com.sleep.getexcel.service.impl;

import com.csvreader.CsvWriter;
import com.mysql.cj.util.StringUtils;
import com.sleep.getexcel.service.SteelService;
import com.sleep.getexcel.vo.FilePath;
import com.sleep.getexcel.vo.Result;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * @author Sleep-6000
 * @date 2022/8/3
 */
@Service
public class SteelServiceImpl implements SteelService {

    private static final String EXTENSION_XLS = "xls";
    private static final String EXTENSION_XLSX = "xlsx";
    private static final String FIRST = "-1#炉";
    private static final String SECOND = "-2#炉";

    @Override
    public Result steel(FilePath filePath) {
        String readPath = filePath.getReadPath();
        String writePath = filePath.getWritePath();
        if (StringUtils.isNullOrEmpty(readPath) || StringUtils.isNullOrEmpty(writePath)) {
            return Result.fail(10003, "参数错误");
        }
        if (!preReadCheck(readPath)) {
            return Result.fail(10001, "文件不存在");
        }
        // 获取workbook对象
        Workbook workbook = null;

        try {
            workbook = this.getWorkbook(readPath);
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                return Result.fail(404, "文件为空");
            }

            int firstRowIndex = sheet.getFirstRowNum();
            int lastRowIndex = sheet.getLastRowNum();

            // 读取首行 即,表头
            Row firstRow = sheet.getRow(firstRowIndex);
            List<String> firstList = new ArrayList<>();
            for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
                Cell cell = firstRow.getCell(i);
                String cellValue = getCellValue(cell, true);
                firstList.add(cellValue);
            }
            // 读取数据行
            CsvWriter csvWriter = new CsvWriter(writePath, ',', StandardCharsets.UTF_8);
            String[] row = new String[3];
            for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
                // 当前行
                Row currentRow = sheet.getRow(rowIndex);
                if (currentRow == null) {
                    continue;
                }
                // 首列
                int firstColumnIndex = currentRow.getFirstCellNum();
                // 最后一列
                int lastColumnIndex = currentRow.getLastCellNum();
                StringBuilder sb = new StringBuilder();
                StringBuilder sb1 = new StringBuilder();
                StringBuilder sb2 = new StringBuilder();

                String name = "";

                for (int columnIndex = firstColumnIndex; columnIndex < lastColumnIndex; columnIndex++) {
                    // 当前单元格
                    Cell currentCell = currentRow.getCell(columnIndex);
                    // 当前单元格的值
                    String currentCellValue = getCellValue(currentCell, true);
                    if (columnIndex == firstColumnIndex) {
                        name = currentCellValue;
                    } else {
//                        sb1.append(name).append(firstList.get(columnIndex));
                        sb1.append(name).append(firstList.get(columnIndex)).append(FIRST);
                        sb2.append(currentCellValue).append("-1");
                        row[0] = sb1.toString();
                        row[1] = sb2.toString();
//                        row[1]=currentCellValue;
                        csvWriter.writeRecord(row);
//                        csvWriter.writeRecord(row);
                        sb1.setLength(0);
                        sb2.setLength(0);

                        sb1.append(name).append(firstList.get(columnIndex)).append(SECOND);
                        sb2.append(currentCellValue).append("-2");
                        row[0] = sb1.toString();
                        row[1] = sb2.toString();
                        csvWriter.writeRecord(row);
                        sb1.setLength(0);
                        sb2.setLength(0);
                    }
                }
            }
            csvWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.success("success");
    }

    /***
     * <pre>
     * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
     *   xls:HSSFWorkbook
     *   xlsx：XSSFWorkbook
     * @param filePath
     * @return
     * @throws IOException
     * </pre>
     */
    private Workbook getWorkbook(String filePath) throws IOException {
        Workbook workbook = null;
        InputStream is = new FileInputStream(filePath);
        if (filePath.endsWith(EXTENSION_XLS)) {
            workbook = new HSSFWorkbook(is);
        } else if (filePath.endsWith(EXTENSION_XLSX)) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }

    /**
     * 文件检查
     */
    private boolean preReadCheck(String filePath) {
        // 常规检查
        File file = new File(filePath);
        if (!file.exists()) {
            System.out.println("传入的文件不存在：" + filePath);
            return false;
        }
        if (!(filePath.endsWith(EXTENSION_XLS) || filePath.endsWith(EXTENSION_XLSX))) {
            System.out.println("传入的文件不是excel");
            return false;
        }
        return true;
    }

    /**
     * 取单元格的值
     *
     * @param cell       单元格对象
     * @param treatAsStr 为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
     */
    private String getCellValue(Cell cell, boolean treatAsStr) {
        if (cell == null) {
            return "";
        }

        if (treatAsStr) {
            // 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
            // 加上下面这句，临时把它当做文本来读取
            cell.setCellType(STRING);
        }

        if (cell.getCellType() == BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }
}
